package org.ielts.excel;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.Calendar;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.Map;

import org.apache.commons.lang.StringUtils;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFComment;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.ielts.configuration.ConfigurationClass;
import org.ielts.domain.Sex;
import org.ielts.domain.base.Student;
import org.ielts.domain.log.AppointConsultorLog;
import org.ielts.domain.log.FirstRecordLog;
import org.ielts.domain.security.User;
import org.ielts.repository.AppointConsultorLogRepository;
import org.ielts.repository.FirstRecordLogRepository;
import org.ielts.repository.StudentRepository;
import org.ielts.repository.security.UserRepository;
import org.junit.Test;
import org.junit.runner.RunWith;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.jdbc.core.JdbcTemplate;
import org.springframework.jdbc.core.namedparam.NamedParameterJdbcTemplate;
import org.springframework.test.annotation.Rollback;
import org.springframework.test.context.ContextConfiguration;
import org.springframework.test.context.junit4.SpringJUnit4ClassRunner;
import org.springframework.transaction.annotation.Transactional;

import com.yifeng.dao.template.JdbcTemplateHelper;
import com.yifeng.dto.DtoTable;
import com.yifeng.dto.IDtoRow;
import com.yifeng.dto.utils.DtoUtil;
import com.yifeng.poi.CellReader;
import com.yifeng.poi.ExcelUtils;

@RunWith(SpringJUnit4ClassRunner.class)
@ContextConfiguration(classes = { ConfigurationClass.class })
public class CTst {

	@Autowired
	JdbcTemplate jdbc;

	DecimalFormat df = new DecimalFormat("#");

	@Autowired
	StudentRepository studentRepository;

	Logger logger = LoggerFactory.getLogger("excelError");

	/**
	 * 去掉同名同学号，联系方式合并
	 */
	@Test
	@Transactional
	@Rollback(false)
	public void mergeStudent2() {
		DtoTable table = jdbc
				.query("select * from student where name in ( select name from student s group by s.stuid,s.name having count(*) > 1) order by name",
						JdbcTemplateHelper.getDtoTableExtractor());

		HashMap<String, IDtoRow> map = new HashMap<String, IDtoRow>();

		for (IDtoRow iDtoRow : table) {
			String name = (String) iDtoRow.get("name");
			String stuId = (String) iDtoRow.get("stuId");
			String key = (name + "." + stuId);

			String newId = (String) iDtoRow.get("id");

			if (map.containsKey(key)) {
				IDtoRow old = map.get(key);

				String oldPhone = (String) old.get("PHONE");
				String phone = (String) iDtoRow.get("PHONE");

				String oldMobile = (String) old.get("MOBILE");
				String mobile = (String) iDtoRow.get("MOBILE");

				phone = StringUtils.join(new String[] { oldPhone, phone }, ",");
				mobile = StringUtils.join(new String[] { oldMobile, mobile }, ",");

				jdbc.update("update student set PHONE =? ,MOBILE = ? where id =?", phone, mobile, old.get("ID"));

				jdbc.update("update schoollog set STUDENT_ID =? where STUDENT_ID =?", old.get("ID"), newId);
				
				jdbc.update("update SIGNCOURSE set STUDENT_ID =? where STUDENT_ID =?", old.get("ID"), newId);

				jdbc.update("delete from student where id = ?", newId);
			} else {
				map.put(key, iDtoRow);
			}
		}
	}

	/**
	 * 去掉没有学号没有联系方式，而且同名的人
	 */
	@Test
	@Transactional
	@Rollback(false)
	public void mergeStudent() {
		DtoTable table = jdbc
				.query("select * from student where  student.mobile is null and student.phone is null and student.stuid is null",
						JdbcTemplateHelper.getDtoTableExtractor());

		HashMap<String, String> map = new HashMap<String, String>();

		for (IDtoRow iDtoRow : table) {
			String name = (String) iDtoRow.get("name");
			String stuId = (String) iDtoRow.get("stuId");

			String id = (String) iDtoRow.get("id");

			String key = (name + stuId);

			if (map.containsKey(key)) {
				String old = map.get(key);

				jdbc.update("update schoollog set STUDENT_ID =? where STUDENT_ID =?", old, id);

				jdbc.update("delete from student where id = ?", iDtoRow.get("id"));
			} else {
				map.put(key, id);
			}
		}
	}

	/**
	 * 导入带学号的学员
	 * 
	 * @throws IOException
	 * @throws ParseException
	 */
	@Test
	@Transactional
	@Rollback(false)
	public void importStudent() throws IOException, ParseException {

		File file = new File("D:\\Desktop\\导入数据\\所有在读学员.xls");

		DtoTable table = ExcelUtils.book(file).sheet("Sheet2").addColumn("A", "name").addColumn("B", "stuNo")
				.addColumn("B", "another", new CellReader() {

					public Object read(HSSFCell cell) {
						HSSFComment comment = cell.getCellComment();
						if (comment != null) {
							String title = comment.getString().getString();
							return title;
						}
						return null;
					}
				}).parseToTable(0);

		int index = 0;
		for (IDtoRow iDtoRow : table) {
			index++;
			String name = (String) iDtoRow.get("name");
			String stuNo = (String) iDtoRow.get("stuNo");
			if (stuNo != null) {
				stuNo = stuNo.trim();
			}
			String another = (String) iDtoRow.get("another");
			Student student = null;

			StringBuilder stringBuilder = new StringBuilder();
			if (another != null) {
				stringBuilder.append(another);
			}

			if (StringUtils.isBlank(name)) {
				logger.debug("错误:没有姓名的学员");
			} else {
				name = name.trim();

				long i = jdbc.query("SELECT count(*) from student where name = ?",
						JdbcTemplateHelper.getLongExtractor(), name);
				if (i == 1) {
					jdbc.update("UPDATE student set stuId = ?,title=? where name = ?", stuNo, another, name);
				} else {
					student = new Student();
					student.setName(name);
					student.setTitle(stringBuilder.toString());
					student.setStuId(stuNo);
					student.setTitle(another);

					studentRepository.save(student);
				}
			}
		}
	}

	/**
	 * 合并同名同号码的人
	 */
	@Test
	@Transactional
	@Rollback(false)
	public void disName() {
		DtoTable table = jdbcTemplate.query(
				"select name from student group by name having count(name) > 1 order by name",
				JdbcTemplateHelper.getDtoTableExtractor());

		for (int i = 0; i < table.size(); i++) {
			IDtoRow iDtoRow = table.get(i);
			String name = (String) iDtoRow.get("name");

			DtoTable sameNames = jdbc.query("SELECT * FROM STUDENT WHERE NAME = ?",
					JdbcTemplateHelper.getDtoTableExtractor(), name);

			Map<String, String> map = new HashMap<String, String>();
			for (IDtoRow row : sameNames) {
				String m = (String) row.get("mobile");
				String newId = (String) row.get("id");
				String key = m;
				if (StringUtils.isNotBlank(key)) {
					if (map.containsKey(key)) {
						jdbc.update("Update schoollog set student_id = ? where student_id = ?", map.get(key), newId);
						jdbc.update("DELETE from student where id = ?", newId);
					} else {
						map.put(key, newId);
					}
				}
			}
		}

	}

	@Test
	@Transactional
	@Rollback(false)
	public void test2() {
		DtoTable table = jdbc.query("select * from student", JdbcTemplateHelper.getDtoTableExtractor());

		for (IDtoRow iDtoRow : table) {
			String stuId = (String) iDtoRow.get("id");

			String sql = "select * from( select a.consultor_id as c, l.createtime from schoollog l, appointconsultorlog a"
					+ " where l.logtype ='AppointConsultor' and l.id = a.id and createTime is not null and a.consultor_id is not null and l.student_id = ?"
					+ " union select s.consultant_id as c, l.createtime from schoollog l, signcourselog s"
					+ " where l.logtype='SignCourse' and l.id = s.id and createTime is not null and s.consultant_id is not null and l.student_id = ? order by createtime desc ) where rownum = 1";

			IDtoRow row = jdbc.query(sql, JdbcTemplateHelper.getDtoRowMapper(), stuId, stuId);
			if (row != null) {
				String cId = (String) row.get("c");

				jdbc.update("update student set CONSULTOR_ID = ? where id = ?", cId, stuId);
			}
		}
	}

	@Test
	@Transactional
	@Rollback(false)
	public void test3() {
		DtoTable table = jdbc.query("select * from student", JdbcTemplateHelper.getDtoTableExtractor());

		for (IDtoRow iDtoRow : table) {
			String stuId = (String) iDtoRow.get("id");

			String sql = "select * from( select a.consultor_id as c, l.createtime from schoollog l, appointconsultorlog a"
					+ " where l.logtype ='AppointConsultor' and l.id = a.id and createTime is not null and a.consultor_id is not null and l.student_id = ?"
					+ " union select s.consultant_id as c, l.createtime from schoollog l, signcourselog s"
					+ " where l.logtype='SignCourse' and l.id = s.id and createTime is not null and s.consultant_id is not null and l.student_id = ? order by createtime desc ) where rownum = 1";

			IDtoRow row = jdbc.query(sql, JdbcTemplateHelper.getDtoRowMapper(), stuId, stuId);
			if (row != null) {
				String cId = (String) row.get("c");

				jdbc.update("update student set CONSULTOR_ID = ? where id = ?", cId, stuId);
			}
		}
	}

	@Autowired
	UserRepository userRepository;

	@Transactional()
	@Test
	@Rollback(false)
	public void importFirstRecord() {
		Iterable<User> users = userRepository.findAll();
		Map<String, User> map = new HashMap<String, User>();
		for (User u : users) {
			map.put(u.getName().toLowerCase(), u);
		}

		DtoTable dtoTable = jdbc.query("select * from temp", JdbcTemplateHelper.getDtoTableExtractor());
		for (IDtoRow iDtoRow : dtoTable) {
			String name = (String) iDtoRow.get("name");
			String contact = (String) iDtoRow.get("contact");
			String contact1 = (String) iDtoRow.get("contact1");
			String front = (String) iDtoRow.get("front");
			String cc = (String) iDtoRow.get("cc");
			String day = (String) iDtoRow.get("day");
			String time = (String) iDtoRow.get("time");
			String sex = (String) iDtoRow.get("sex");
			String source = (String) iDtoRow.get("source");

			Student student = new Student();
			if (StringUtils.isBlank(name)) {
				continue;
			}
			name = name.trim();
			if ("姓名".equals(name)) {
				continue;
			}

			student.setName(name);

			if ("男".equals(sex)) {
				student.setSex(Sex.Male);
			} else if ("女".equals(sex)) {
				student.setSex(Sex.Female);
			} else {
				student.setSex(Sex.NotSet);
			}

			if (StringUtils.isNotBlank(contact)) {
				contact = contact.trim();
				student.setMobile(contact);
			}

			if (StringUtils.isNotBlank(contact1)) {
				contact1 = contact1.trim();
				student.setPhone(contact1);
			}

			FirstRecordLog firstRecordLog = new FirstRecordLog();
			firstRecordLog.setCampus(null);

			String[] days = StringUtils.splitByCharacterType(day);
			String[] times = StringUtils.splitByCharacterType(time);

			firstRecordLog.setCreateTime(null);

			if (days != null && days.length == 5 && times != null && times.length == 3) {
				try {
					int year = Integer.parseInt(days[0]);
					int month = Integer.parseInt(days[2]);
					int date = Integer.parseInt(days[4]);

					int hour = Integer.parseInt(times[0]);
					int min = Integer.parseInt(times[2]);

					Calendar createTime = Calendar.getInstance();
					createTime.set(year, month - 1, date, hour, min);

					firstRecordLog.setCreateTime(createTime.getTime());

				} catch (Exception ex) {

				}
			} else if (days != null && days.length == 5) {
				try {
					int year = Integer.parseInt(days[0]);
					int month = Integer.parseInt(days[2]);
					int date = Integer.parseInt(days[4]);

					Calendar createTime = Calendar.getInstance();
					createTime.set(year, month - 1, date, 0, 0);

					firstRecordLog.setCreateTime(createTime.getTime());

				} catch (Exception ex) {

				}
			}

			if (firstRecordLog.getCreateTime() != null) {
				if (firstRecordLog.getCreateTime().after(new Date())) {
					firstRecordLog.setCreateTime(null);
				}
			}

			if (StringUtils.isNotBlank(front)) {
				User creator = map.get(front.toLowerCase());

				firstRecordLog.setCreator(creator);
			}

			firstRecordLog.setSource(source);

			String title = iDtoRow.toString();

			StringBuilder sb = new StringBuilder();

			for (char c = 'A'; c < 'Q'; c++) {
				String key = new String(new char[] { c });
				String v = (String) iDtoRow.get(key);
				if (v != null) {
					v = v.trim();
					if (v.length() == 1) {
						char t = v.charAt(0);
						if ((t <= 'z' && t >= 'a') || (t <= 'Z' && t >= 'A')) {
							sb.append(v);
							sb.append("-");
						}
					}
				}
			}

			sb.append(title);

			firstRecordLog.setTitle(sb.toString());

			AppointConsultorLog appointConsultorLog = new AppointConsultorLog();
			appointConsultorLog.setCreateTime(firstRecordLog.getCreateTime());

			if (StringUtils.isNotBlank(cc)) {
				User consultor = map.get(cc.toLowerCase());

				appointConsultorLog.setConsultor(consultor);

				appointConsultorLog.setLogTime(firstRecordLog.getCreateTime());

				appointConsultorLog.setStudent(student);

				appointConsultorLog.setTitle(cc);
			}

			studentRepository.save(student);

			firstRecordLog.setStudent(student);
			firstRecordLogRepository.save(firstRecordLog);

			if (appointConsultorLog.getConsultor() != null) {
				appointConsultorLogRepository.save(appointConsultorLog);
			}
		}
	}

	@Autowired
	AppointConsultorLogRepository appointConsultorLogRepository;

	@Autowired
	FirstRecordLogRepository firstRecordLogRepository;

	/**
	 * 导入原始信息
	 * 
	 * @throws IOException
	 */
	@Test
	public void test() throws IOException {
		String sql = "insert into temp" + "  (id, a, b, c, d, e, f, g, h, i, j, k, l, m, n, o, p, no, sheet)"
				+ "values" + "  (:id, :a, :b, :c, :d, :e, :f, :g, :h, :i, :j, :k, :l, :m, :n, :o, :p, :no, :sheet)";

		File file = new File("D:\\Desktop\\新版来访登记.xls");

		FileInputStream fileInputStream = new FileInputStream(file);
		HSSFWorkbook book = new HSSFWorkbook(fileInputStream);

		int sheetNum = book.getNumberOfSheets();
		int sum = 0;
		for (int i = 0; i < sheetNum; i++) {
			HSSFSheet sheet = book.getSheetAt(i);

			String sheetName = sheet.getSheetName();

			Iterator<Row> rowIterator = sheet.rowIterator();

			while (rowIterator.hasNext()) {
				Row row = rowIterator.next();
				int rowIndex = row.getRowNum();

				Map<String, Object> value = new HashMap<String, Object>();

				for (Cell cell : row) {
					int c = cell.getColumnIndex();

					String a = new String(new char[] { (char) ('a' + c) });

					String v = null;

					if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
						String formatString = cell.getCellStyle().getDataFormatString();

						if (StringUtils.isNotBlank(formatString)) {
							if ("@".equals(cell.getCellStyle().getDataFormatString())) {
								v = df.format(cell.getNumericCellValue());
							} else if ("General".equals(cell.getCellStyle().getDataFormatString())) {
								v = df.format(cell.getNumericCellValue());
							} else {
								if ("yyyy\\/m\\/d".equals(formatString)) {
									formatString = "yyyy-MM-dd";
								}
								SimpleDateFormat sdf = new SimpleDateFormat(formatString);

								Date date = cell.getDateCellValue();

								v = sdf.format(date);
							}
						} else {

							double d = cell.getNumericCellValue();

							v = df.format(d);
						}
					} else {
						v = cell.toString();
					}

					if (v != null) {
						if (v.length() > 200) {
							System.out.println(v);
							v = v.substring(0, 200);
						}
						v = v.trim();
					}

					value.put(a, v);
				}

				for (char t = 'a'; t < 'q'; t++) {
					String key = new String(new char[] { t });
					if (!value.containsKey(key)) {
						value.put(key, null);
					}
				}

				value.put("id", sum);
				value.put("no", rowIndex);
				value.put("sheet", sheetName);

				jdbcTemplate.update(sql, value);

				sum++;
			}
		}
	}

	@Autowired
	NamedParameterJdbcTemplate jdbcTemplate;

}
